<?php defined('BASEPATH') or exit('No direct script access allowed');
class Statistic_m extends MY_Model
{
	public function __construct(){
		parent::__construct();
		$this->set_table_name("user");
	}

	public  function getUserStatistic(){

		$sql = "SELECT SUM(CASE user_type WHEN 'USER' THEN 1 ELSE 0 END) AS user_count,"
		." SUM(CASE user_type WHEN 'AGENT' THEN 1 ELSE 0 END) AS agent_count,"
		." SUM(CASE user_type WHEN 'AFFILIATE' THEN 1 ELSE 0 END) AS affiliate_count"
		." FROM l_user";

		return $this->db->query($sql, array())->first_row();
	}

	public function getRunningBetStatistic(){
		$sql="SELECT SUM(1)  AS bet_count, SUM(stake) AS stake"
		." FROM l_transaction WHERE status='RUNNING'";
		return $this->db->query($sql, array())->first_row();
	}

	public  function getTodayWinLoseStatistic(){

		$sql = "SELECT SUM(1)  AS bet_count, SUM(stake) AS stake, SUM(win_lose - stake) AS win_lose"
		." FROM l_transaction WHERE status='WIN' OR status='LOSE'"
		." AND date(settle_date) = ?";

		$today = new DateTime();
		return $this->db->query($sql, array($today->format('Y-m-d')))->first_row();
	}

	public  function getCurrentMonthWinLoseStatistic(){

		$sql = "SELECT SUM(1)  AS bet_count, SUM(stake) AS stake,SUM(win_lose - stake) AS win_lose"
		." FROM l_transaction WHERE status='WIN' OR status='LOSE'"
		." AND date(settle_date) BETWEEN ? AND ?";

		$today = new DateTime();
		$first = date('Y-m-d', mktime(0, 0, 0, $today->format('m'), 1, $today->format('Y')));

		return $this->db->query($sql, array($first, $today->format('Y-m-d')))->first_row();
	}

	public  function getTodayTransferStatistic(){

		$sql = "SELECT SUM(CASE trans_type WHEN 'DEPOSIT' THEN win_lose - stake ELSE 0 END) AS deposit"
		.", SUM(CASE trans_type WHEN 'WITHDRAW' THEN win_lose - stake ELSE 0 END) AS withdraw"
		.", SUM(CASE trans_type WHEN 'TRANSFER' THEN win_lose - stake ELSE 0 END) AS transfer"
		.", SUM(CASE trans_type WHEN 'BONUS' THEN win_lose - stake ELSE 0 END) AS bonus"
		." FROM l_transaction WHERE (trans_type ='DEPOSIT' OR trans_type = 'WITHDRAW' OR trans_type = 'TRANSFER' OR trans_type = 'BONUS')"
		." AND date(settle_date) = ?";

		$today = new DateTime();
		
		return $this->db->query($sql, array($today->format('Y-m-d')))->first_row();
	}

	public  function getCurrentMonthTransferStatistic(){

		$sql = "SELECT SUM(CASE trans_type WHEN 'DEPOSIT' THEN win_lose - stake ELSE 0 END) AS deposit"
		.", SUM(CASE trans_type WHEN 'WITHDRAW' THEN win_lose - stake ELSE 0 END) AS withdraw"
		.", SUM(CASE trans_type WHEN 'TRANSFER' THEN win_lose - stake ELSE 0 END) AS transfer"
		.", SUM(CASE trans_type WHEN 'BONUS' THEN win_lose - stake ELSE 0 END) AS bonus"
		." FROM l_transaction WHERE (trans_type ='DEPOSIT' OR trans_type = 'WITHDRAW' OR trans_type = 'TRANSFER' OR trans_type = 'BONUS')"
		." AND date(settle_date) BETWEEN ? AND ?";

		$today = new DateTime();
		$first = date('Y-m-d', mktime(0, 0, 0, $today->format('m'), 1, $today->format('Y')));

		return $this->db->query($sql, array($first, $today->format('Y-m-d')))->first_row();
	}
}